# _*_ coding:utf-8 _*_
# @File  : personal.py
# @Time  : 2022-01-26  10:09
# @Author: zizle
import datetime
import json
from typing import List, Optional
from fastapi import APIRouter, Body, Header, Query, BackgroundTasks
from pydantic import BaseModel, constr, conint

from db import FAConnection, RuizyEDBConnection
from global_constant import VARIETY
from v1_all_api.all_response import AllResponse
from v1_all_api.all_utils import datetime_utils, datalib_utils, data_utils
from v1_all_api import constant
from hutool import security
from configs import FILE_STORAGE, WEB_HOST, STATIC_FILE_HOST

datalib_personal_api = APIRouter()

# ------------------- 指标管理任务


def add_personal_index_row(sheet_id, formula):  # 添加个人品种指标库的记录
    # 从sheet_id得到品种及用户id，从formula得到指标，根据指标获得数据库id
    with FAConnection() as fa:
        fa.execute('SELECT id,user_id,variety_en FROM datalib_variety_sheet WHERE id=%s LIMIT 1;', [sheet_id])
        sheet = fa.fetchone()
    if not sheet:
        return
    with RuizyEDBConnection() as rdb:
        rdb.execute('SELECT id FROM all_ruizy_sheet WHERE source_plat=%s AND source_id=%s LIMIT 1;',
                    formula.split('_'))
        ret = rdb.fetchone()
        now = datetime.datetime.now().strftime('%Y-%m-%d_%H%M%S')
        if not ret:
            with open(f'debug/bg_task_{now}.log', 'a+', encoding='utf8') as lg:
                lg.write(f'{now}：添加用户指标失败了,找不到{formula}的ID记录。\n')
            return
        # 添加
        rdb.execute("INSERT IGNORE INTO all_personal_sheet (user_id,variety_en,ruizy_sheet_id) VALUES (%s,%s,%s);",
                    [sheet['user_id'], sheet['variety_en'], ret['id']])

        with open(f'debug/bg_task_{now}.log', 'a+', encoding='utf8') as lg:
            lg.write(f'{now}：添加用户指标成功，formula={formula}。\n')

# -------------------


# 查询各人的指标表
@datalib_personal_api.get('/indexes/')
def get_personal_indexes(ruizy_token: str = Header(...), user: int = Query(...), variety: str = Query(None),
                         page: int = Query(1), page_size: int = Query(30)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    sql = """
        SELECT a.user_id,a.variety_en,b.* 
        FROM all_personal_sheet As a INNER JOIN all_ruizy_sheet As b ON a.ruizy_sheet_id=b.id
        WHERE a.user_id=%s
    """
    prm = [user]
    if variety and variety != '0':
        sql += ' AND a.variety_en=%s LIMIT %s,%s;'
        prm.append(variety)
        prm.append((page - 1) * page_size)
        prm.append(page_size)
    else:
        sql += ' LIMIT %s,%s;'
        prm.append((page - 1) * page_size)
        prm.append(page_size)
    rdb_conn = RuizyEDBConnection()

    if user == 0:
        if not variety:
            return AllResponse.validate_error(msg='不分用户时，品种必须指定。')
        sql = """
            SELECT a.user_id,a.variety_en,b.* 
            FROM all_personal_sheet As a INNER JOIN all_ruizy_sheet As b ON a.ruizy_sheet_id=b.id
            WHERE a.variety_en=%s LIMIT %s,%s;
        """
        prm = [variety, (page - 1) * page_size, page_size]

    ret = rdb_conn.query_paginator(sql, prm, page=page, page_size=page_size)
    for row in ret['data']:
        row['variety_name'] = VARIETY.VARIETY_NAMES.get(row['variety_en'], row['variety_en'])
    return AllResponse.operate_successfully(data=ret)


class SheetItem(BaseModel):
    name: constr(max_length=32)
    variety_en: constr(min_length=1)
    is_open: int = 1


@datalib_personal_api.post('/sheet/')  # 个人添加一个数据表
async def add_personal_sheet(ruizy_token: str = Header(...), sheet_item: SheetItem = Body(...)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    # return AllResponse.server_error()
    # 添加一个数据表
    sql = 'INSERT INTO datalib_variety_sheet (name,variety_en,user_id,is_open) VALUES (%s,%s,%s,%s);'
    prm = [sheet_item.name, sheet_item.variety_en, user_obj['uid'], sheet_item.is_open]
    db_conn = FAConnection()
    success, new_id = db_conn.insert(sql, prm)
    resp_data = {"id": new_id, "name": sheet_item.name}
    return AllResponse.create_successfully(data=resp_data) if success else AllResponse.server_error()


@datalib_personal_api.delete('/sheet/')  # 删除一个数据表
async def delete_personal_sheet(ruizy_token: str = Header(...), sheet_id: int = Body(..., ge=1, embed=True)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    db_conn = FAConnection()
    if user_obj['role_code'] not in [constant.ADMIN_ROLE_CODE]:
        sheet = db_conn.query('SELECT id,user_id FROM datalib_variety_sheet WHERE id=%s LIMIT 1;', param=[sheet_id],
                              fetchone=True, keep_conn=True)[0]
        if not sheet or sheet['user_id'] != user_obj['uid']:
            db_conn.close()
            return AllResponse.validate_error(msg='请不要删除他人创建的表!')
    # 删除表，删除表下的列
    del_sheet = "DELETE FROM datalib_variety_sheet WHERE id=%s LIMIT 1;"
    del_column = "DELETE FROM datalib_sheet_column WHERE sheet_id=%s;"
    del_param = [sheet_id]
    success = db_conn.execute_tasks([del_sheet, del_column], param_list=[del_param, del_param])
    return AllResponse.operate_successfully() if success else AllResponse.server_error()


class SheetColumnItem(BaseModel):
    id: int = -1
    name: constr(max_length=64)
    sheet_id: conint(ge=1)
    formula: constr(max_length=512)
    frequency: constr(max_length=1)
    unit: constr(max_length=32) = ''
    is_diff: conint(ge=0, le=1) = 0


@datalib_personal_api.post('/sheet/column/')  # 添加一个数据列
async def add_column_detail(bg_tasks: BackgroundTasks, column_item: SheetColumnItem = Body(...)):
    if not datalib_utils.formula_is_correct(column_item.formula):
        return AllResponse.validate_error(msg='公式格式有误,添加失败！')
    db_conn = FAConnection()
    # 检查该表下有多少数据列了，最多只能加16个
    count_sql = 'SELECT COUNT(id) AS count FROM datalib_sheet_column WHERE sheet_id=%s;'
    count = db_conn.query(count_sql, [column_item.sheet_id], keep_conn=True)[0]['count']
    if count >= 16:
        db_conn.close()
        return AllResponse.validate_error(msg='添加失败:一个表内只能添加{}条指标。'.format(count))
    # 直接添加
    sql = 'INSERT INTO datalib_sheet_column (name,sheet_id,formula,frequency,is_diff,unit) ' \
          'VALUES (%s,%s,%s,%s,%s,%s);'
    prm = [column_item.name, column_item.sheet_id, column_item.formula, column_item.frequency, column_item.is_diff,
           column_item.unit]

    success, nid = db_conn.insert(sql, prm)
    resp_data = {
        'id': nid,
        'name': column_item.name,
        'formula': column_item.formula,
        'frequency': column_item.frequency,
        'unit': column_item.unit
    }
    # 添加成功，且不是同比，环比计算，需要在个人品种指标库里添加数据
    if column_item.is_diff or len(column_item.formula) > 16:  # >16公式的，忽略
        pass
    else:
        bg_tasks.add_task(add_personal_index_row, column_item.sheet_id, column_item.formula)
    return AllResponse.operate_successfully(data=resp_data) if success else AllResponse.server_error()


@datalib_personal_api.delete('/sheet/column/')  # 删除一个数据列
async def delete_column_detail(col_item: SheetColumnItem = Body(...)):
    # 此处不考虑图形是否根据这个指标作图，因为图形是单独获取数据的,直接删除该表下的指标即可
    delete_sql = 'DELETE FROM datalib_sheet_column WHERE id=%s AND sheet_id=%s;'
    delete_prm = [col_item.id, col_item.sheet_id]
    db_conn = FAConnection()
    _, success = db_conn.execute(delete_sql, delete_prm)
    return AllResponse.operate_successfully() if success else AllResponse.server_error()


@datalib_personal_api.get('/sheet/column/')  # 获取一个模板下的指标信息
async def get_template_detail(sheet: int = Query(..., ge=1)):  # sheet为sheet_id
    sql = 'SELECT id,name,sheet_id,formula,frequency,unit,is_diff,sorted FROM datalib_sheet_column ' \
          'WHERE sheet_id=%s;'
    prm = [sheet]
    db_conn = FAConnection()
    records = db_conn.query(sql, prm)
    return AllResponse.operate_successfully(data=records)


class DataColumnItem(BaseModel):
    id: conint(ge=1)


@datalib_personal_api.post('/sheet/columns/data/')  # 获取模板详情实际数据
async def get_template_detail_data(indexes: List[DataColumnItem] = Body(..., embed=True),
                                   excel_name: str = Body(None, embed=True)):
    # 查询模板详情
    detail_sql = 'SELECT id,name,sheet_id,formula,frequency,is_diff,unit FROM datalib_sheet_column WHERE id IN %s;'
    detail_prm = [[i.id for i in indexes]]
    db_conn = FAConnection()
    template_records = db_conn.query(detail_sql, detail_prm)
    if not len(template_records):
        return AllResponse.server_no_content(msg='查询的表数据不存在!')
    # 解析数据中的公式，得到需要的表
    df, table_headers = await datalib_utils.get_index_formula_dataframe(column_templates=template_records)
    if df.empty:
        return AllResponse.server_no_content(msg='提取数据失败，服务器没有数据!')
    if excel_name:  # 导出excel文件
        now_ts = int(datetime.datetime.now().timestamp() * 1000)
        excel_filename = f'{FILE_STORAGE}Temp/DataSheet/{excel_name}{now_ts}.xls'
        header = ['日期'] + [h['name'] for h in table_headers]
        df.to_excel(excel_filename, index=False, header=header)
        resp_data = {'file_url': STATIC_FILE_HOST + f'Temp/DataSheet/{excel_name}{now_ts}.xls'}
    else:
        resp_data = {'table_value': df.to_dict(orient='records'), 'table_header': table_headers}
    return AllResponse.operate_successfully(data=resp_data)


class ChartItem(BaseModel):
    chart_type: str
    name: constr(min_length=1)
    sheet_id: int
    variety: str
    options: dict
    indication: list
    watermark: constr(max_length=8) = ''
    is_open: conint(ge=0, le=1) = 1
    startdate: str = None
    enddate: str = None
    xaxis_format: str


class ModifyItem(BaseModel):
    chart_id: conint(ge=1)
    name: constr(min_length=1)
    xaxis_format: str
    xaxis_rotate: int = 0
    startdate: str = None
    enddate: str = None
    watermark: constr(max_length=8) = ''
    left_min: Optional[float]
    left_max: Optional[float]
    right_min: Optional[float]
    right_max: Optional[float]
    sorted: int = 1


@datalib_personal_api.post('/chart/')  # 保存一个数据图形
async def save_chart(ruizy_token: str = Header(...), chart_item: ChartItem = Body(...)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    if chart_item.chart_type not in constant.CHART_TYPES.keys():
        return AllResponse.validate_error(msg='图形类别不支持!')
    startdate, enddate = None, None
    if chart_item.startdate:
        startdate = datetime_utils.auth_datetime_string(chart_item.startdate, f='%Y-%m-%d')
    if chart_item.enddate:
        enddate = datetime_utils.auth_datetime_string(chart_item.enddate, f='%Y-%m-%d')
    if chart_item.xaxis_format not in ['%Y', '%Y-%m', '%Y-%m-%d']:
        return AllResponse.validate_error(msg='参数xaxis_format错误!')
    # 季节图形，横轴格式必须为%Y-%m-%d
    if chart_item.chart_type == 'season':
        chart_item.xaxis_format = '%Y-%m-%d'
    options = json.dumps(chart_item.options, ensure_ascii=False)
    indication = json.dumps(chart_item.indication, ensure_ascii=False)
    # 保存到数据表中
    insert_sql = 'INSERT INTO datalib_variety_chart (chart_type,user_id,sheet_id,name,variety,startdate,enddate,' \
                 'xaxis_format,options,indication,is_open,watermark) ' \
                 'VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);'
    insert_prm = [chart_item.chart_type, user_obj['uid'], chart_item.sheet_id, chart_item.name, chart_item.variety,
                  startdate, enddate, chart_item.xaxis_format, options, indication, chart_item.is_open,
                  chart_item.watermark]
    db_conn = FAConnection()
    success, _ = db_conn.insert(insert_sql, insert_prm)
    return AllResponse.create_successfully() if success else AllResponse.server_error()


@datalib_personal_api.put('/chart/')  # 修改一个图形的配置
async def modify_chart(ruizy_token: str = Header(...), modify_item: ModifyItem = Body(...)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    startdate, enddate = None, None
    if modify_item.startdate:
        startdate = datetime_utils.auth_datetime_string(modify_item.startdate, f='%Y-%m-%d')
    if modify_item.enddate:
        enddate = datetime_utils.auth_datetime_string(modify_item.enddate, f='%Y-%m-%d')
    if modify_item.xaxis_format not in ['%Y', '%Y-%m', '%Y-%m-%d']:
        return AllResponse.validate_error(msg='参数xaxis_format错误!')
    # 查询出图形配置，并修改相关数据后修改图形配置
    db_conn = FAConnection()
    chart_obj = db_conn.query('SELECT id,options,user_id FROM datalib_variety_chart WHERE id=%s LIMIT 1;',
                              [modify_item.chart_id], keep_conn=True, fetchone=True)[0]
    if not chart_obj:
        db_conn.close()
        return AllResponse.server_no_content()
    if user_obj['role_code'] != constant.ADMIN_ROLE_CODE and user_obj['uid'] != chart_obj['user_id']:
        db_conn.close()
        return AllResponse.validate_error(msg='您不能修改其他人的图形配置.')
    # 修改配置
    chart_option = json.loads(chart_obj['options'])
    chart_option['title']['text'] = modify_item.name
    chart_option['yAxis'][0]['min'] = modify_item.left_min
    chart_option['yAxis'][0]['max'] = modify_item.left_max
    chart_option['yAxis'][1]['min'] = modify_item.right_min
    chart_option['yAxis'][1]['max'] = modify_item.right_max

    if 'axisLabel' in chart_option['xAxis'][0].keys():
        chart_option['xAxis'][0]['axisLabel']['rotate'] = modify_item.xaxis_rotate  # 横轴旋转角度
    else:
        chart_option['xAxis'][0].update({
            'axisLabel': {'rotate': modify_item.xaxis_rotate}
        })

    op_sql = 'UPDATE datalib_variety_chart SET name=%s,startdate=%s,enddate=%s,xaxis_format=%s,' \
             'watermark=%s,sorted=%s,options=%s WHERE id=%s LIMIT 1;'
    op_prm = [modify_item.name, startdate, enddate, modify_item.xaxis_format, modify_item.watermark,
              modify_item.sorted, json.dumps(chart_option, ensure_ascii=False), modify_item.chart_id]
    _, success = db_conn.execute(op_sql, op_prm)
    # 其他参数返回，相应界面变化
    resp_data = {
        'name': modify_item.name,
        'watermark': modify_item.watermark,
        'sorted': modify_item.sorted,
        'startdate': startdate,
        'enddate': enddate
    }
    return AllResponse.operate_successfully(data=resp_data) if success else AllResponse.server_error()


@datalib_personal_api.delete('/chart/')  # 删除一个图形
async def delete_chart(ruizy_token: str = Header(...), cid: int = Query(..., ge=1)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    # 删除图形，如果不是管理员只能删除自己创建的图形
    op_sql = 'DELETE FROM datalib_variety_chart WHERE id=%s AND IF("9999"=%s,TRUE,user_id=%s) LIMIT 1;'
    op_prm = [cid, str(user_obj['role_code']), user_obj['uid']]
    db_conn = FAConnection()
    _, success = db_conn.execute(op_sql, op_prm)
    return AllResponse.operate_successfully() if success else AllResponse.server_error()


@datalib_personal_api.post('/dbIndexGroup/')  # 用户创建一个自己的指标分组库
def create_my_datalib_group():
    return AllResponse.operate_successfully()


@datalib_personal_api.get('/dbIndexGroup/')  # 获取自己的指标组或指标标
def retrieve_group_or_indexes(ruizy_token: str = Header(...), ven: str = Query(...), gid: int = Query(0)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    with FAConnection() as cursor:
        cursor.execute("""
            SELECT id,name,parent_id,vcode FROM datalib_user_index_group WHERE parent_id=%s AND vcode=%s AND user_id=%s AND is_active=1
            ORDER BY sort_num;
        """, [gid, ven, user_obj['uid']])
        groups = list(cursor.fetchall())
    res_data = {
        'data': [{**g, 'variety': ven} for g in groups]
    }
    return AllResponse.operate_successfully(data=res_data)


@datalib_personal_api.get('/dbIndexGroupTree/')  # 获取自己的指标分组库,Tree
def retrieve_my_datalib_group(ruizy_token: str = Header(...), v: str = Query(...)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    with FAConnection() as cursor:
        cursor.execute("""
            SELECT id,name,parent_id,vcode FROM datalib_user_index_group WHERE vcode=%s AND user_id=%s AND is_active=1
            ORDER BY sort_num;
        """, [v, user_obj['uid']])
        groups = list(cursor.fetchall())
    ret_tree = data_utils.list2tree(data=groups, pkey='parent_id', ckey='id', fvalue=str(0))
    return AllResponse.operate_successfully(data={'groups': ret_tree})


@datalib_personal_api.get('/dbGroupIndexes/')  # 获取自己组下的指标列表
def retrieve_my_group_indexes(ruizy_token: str = Header(...), gid: int = Query(0)):
    if not gid:
        return AllResponse.operate_successfully(data={'indexes': []})
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    with FAConnection() as cursor:
        cursor.execute("""
            SELECT id,name,formula,frequency,unit,is_diff,group_id,source_plat,source_id,vcode,sorted FROM datalib_user_group_index WHERE group_id=%s AND is_active=1
            ORDER BY sorted;
        """, [gid])
        index_list = list(cursor.fetchall())
    for row in index_list:
        row['leaf'] = True
        row['is_index'] = True
    return AllResponse.operate_successfully(data={'indexes': index_list})


@datalib_personal_api.delete('/dbGroupIndexes/')  # 移出组下的指标
def remove_my_group_index(ruizy_token: str = Header(...), row_id: int = Query(...)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    with FAConnection() as cursor:
        cursor.execute("DELETE FROM datalib_user_group_index WHERE id=%s AND user_id=%s;", [row_id, user_obj['uid']])
    return AllResponse.operate_successfully()
